# encoding:utf-8
# 清理 tbox 中错误手机号
import pymysql
import json
import requests
import traceback
import sys

# 联调 flux 项目流量接口
#url = "http://129.204.101.173/v1/flux/query_flow"
# 预发布 flux 项目流量接口
#url = "http://10.123.2.120/v1/flux/query_flow"
# 生产 10.123.27.5
url = "http://10.123.27.5/v1/flux/query_flow"
headers = {
    'Content-Type': "application/json",
    'CompanyID': "1"
}

# 建立mysql链接
# db = pymysql.connect(host='10.2.3.5',
#                      port=3306,
#                      user='root',
#                      passwd='sirun@2018',
#                      db='bss-gq',
#                      charset='utf8')
# db = pymysql.connect(host='10.123.2.10',
#                      port=3306,
#                      user='sirun',
#                      passwd='sirun@2019',
#                      db='bss-gq',
#                      charset='utf8')
# 生产
db = pymysql.connect(host='10.123.16.94',
                     port=3306,
                     user='sirun2',
                     passwd='123^54Sirun',
                     db='bss-gq',
                     charset='utf8')
def printt(s):
    print(s)
    f = open('cleanTboxNO.log', 'a+', encoding='utf-8')
    print(s,file=f)
    f.close()

def getTboxes():
    # SQL 查询语句
    sql = "SELECT s.iccid,s.operator,s.msisdn FROM tab_vehicle_info vi JOIN tab_tbox t ON vi.tbox_id = t.tbox_id AND t.delete_type = 0 JOIN tab_sim s ON s.sim_id = t.sim_id AND s.msisdn IS NOT NULL JOIN tab_vehicle_model vm ON vm.vehicle_model_id = vi.vehicle_model_id JOIN tab_vehicle_series vs ON vs.vehicle_series_id = vm.vehicle_series_id AND vi.deleteType = 0 GROUP BY s.iccid ORDER BY vi.create_date DESC"

    # 执行SQL语句
    cursor.execute(sql, ())
    # 获取一条记录列表
    return cursor.fetchall()


cursor = db.cursor(cursor=pymysql.cursors.DictCursor)  # 使用cursor()方法获取操作游标

log_file = open("message.log", "a+", encoding="utf-8")
sys.stdout = log_file
num = 0
tboxes = getTboxes()
for tbox in tboxes:
    num = num + 1
    if num % 100 == 0:
        printt("num: "+str(num))
        log_file.close()
        log_file = open("message.log", "a+", encoding="utf-8")
        sys.stdout = log_file

    iccid = tbox['iccid']
    operator = tbox['operator']
    if operator is None:
        printt(iccid+",0,operator empty in database")
        continue
    if iccid is None:
        continue

    payload = "{\"iccid\": \"" + iccid + "\",\"provider\":\""
    if "中国移动" == operator:
        payload = payload + "0"
    else:
        payload = payload + "1"
    payload = payload + "\"}"
    # printt(payload)
    try:
        response = requests.request(
            "POST", url, data=payload.encode('utf-8'), headers=headers)
    except Exception as e:
        # 打印错误信息
        traceback.print_exc()
        continue

    jsStr = str(response.content, 'utf-8')
    # printt(jsStr)
    js = json.loads(jsStr)
    if 'code' not in js:
        printt(iccid+",0,"+operator)
        continue
    if 0 != js['code']:
        printt(iccid+",1,"+operator+","+js['msg'])
        continue

    if 'data' not in js:
        printt(iccid+",2,"+operator)
        continue

    data = js['data']
    if "中国移动" == operator:
        if 'simCard' not in data:
            printt(iccid+",3,"+operator+","+js['msg'])
            continue

        simCard = data['simCard']
        if simCard is None:
            printt(iccid+",4,"+operator)
            continue

        if simCard != tbox['msisdn']:
            printt(iccid+",5,"+operator+",iccid 与号码不匹配,"+tbox['msisdn'])
            continue
    else:
        if 'sim' not in data:
            printt(iccid+",6,"+operator+","+js['msg'])
            continue

        sim = data['sim']
        if sim is None:
            printt(iccid+",7,"+operator)
            continue

        if sim != tbox['msisdn']:
            printt(iccid+",8,"+operator+",iccid 与号码不匹配,"+tbox['msisdn'])
            continue

log_file.close()
